﻿using System;
using System.Data;
using System.Data.OracleClient;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Text;

namespace xfsale.lib
{
    public static class Query
    {
        private static OracleConnection conn = App.Conn;

        public static TransAccount GetTransAccount(string transAccountID)
        {
            TransAccount result = null;

            OracleCommand command = conn.CreateCommand();
            command.CommandText = @"select c.*, t.transAcctStatus from AcctTrans t, AcctCust c ";
            command.CommandText += "where t.custAccountID = c.custAccountID ";
            command.CommandText += "and t.transAccountID = :transAccountID";
            command.Parameters.Clear();
            command.Parameters.Add("transAccountID", OracleType.VarChar).Value = transAccountID;
            OracleDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                result = new TransAccount();
                result.TransAccountID = transAccountID;
                result.CustAccountID = Convert.ToString(reader["CustAccountID"]);
                result.CustType = Convert.ToString(reader["CustType"]);
                result.CustName = Convert.ToString(reader["CustName"]);
                result.CustNameAbbr = Convert.ToString(reader["CustNameAbbr"]);
                result.CertType = Convert.ToString(reader["CertType"]);
                result.CertNo = Convert.ToString(reader["CertNo"]);
                result.TransAcctStatus = Convert.ToString(reader["TransAcctStatus"]);
            }
            reader.Close();

            return result;
        }

        public static List<ValueTextPair> GetTACodes()
        {
            List<ValueTextPair> list = new List<ValueTextPair>();

            OracleCommand command = conn.CreateCommand();
            command.CommandText = @"select tacode, (tacode||' '||shortname) shortname from TA order by taCode";
            command.Parameters.Clear();
            OracleDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                ValueTextPair pair = new ValueTextPair();
                pair.Value = Convert.ToString(reader["TACode"]);
                pair.Text = Convert.ToString(reader["ShortName"]);
                list.Add(pair);
            }
            reader.Close();

            return list;
        }

        public static List<ValueTextPair> GetManagerCodes()
        {
            List<ValueTextPair> list = new List<ValueTextPair>();

            OracleCommand command = conn.CreateCommand();
            command.CommandText = @"select managerCode, (managerCode||' '||managerNameAbbr) managerNameAbbr from FundManager order by managerCode";
            command.Parameters.Clear();
            OracleDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                ValueTextPair pair = new ValueTextPair();
                pair.Value = Convert.ToString(reader["ManagerCode"]);
                pair.Text = Convert.ToString(reader["ManagerNameAbbr"]);
                list.Add(pair);
            }
            reader.Close();

            return list;
        }

        public static List<ValueTextPair> GetFundCodes()
        {
            List<ValueTextPair> list = new List<ValueTextPair>();

            OracleCommand command = conn.CreateCommand();
            command.CommandText = @"select FundCode, (FundCode||' '||FundNameAbbr) FundNameAbbr from Fund order by FundCode";
            command.Parameters.Clear();
            OracleDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                ValueTextPair pair = new ValueTextPair();
                pair.Value = Convert.ToString(reader["FundCode"]);
                pair.Text = Convert.ToString(reader["FundNameAbbr"]);
                list.Add(pair);
            }
            reader.Close();

            return list;
        }
    }
}
